﻿#region "Imports"
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using System.Text;
using Practico2.DataTypes;

#endregion

namespace Practico2.DataAccess
{

    /// <summary>
    /// Acceso a datos para usuarios de los servicios web
    /// </summary>
    public class UsuarioWebDataAccess
    {

        public static int InsertUsuarioWeb(DTUsuarioWeb toInsert)
        {
            int toReturn = 0;
            SqlTransaction transaction = TransactionManager.GetTransaction();
            SqlConnection connection = null;
            SqlDataReader reader = null;
            string insertCommandText = "INSERT INTO UsuarioWeb([NombreUsuarioWeb],[ContraseniaUsuarioWeb]) VALUES (@NombreUsuarioWeb,@ContraseniaUsuarioWeb) ; SELECT @@Identity";
            bool useTransaction = (transaction != null);
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }

            SqlCommand command;


            if (useTransaction)
            {
                command = new SqlCommand(insertCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(insertCommandText, connection);
            }

            if (toInsert.IsNombreUsuarioWebNull)
            {
                command.Parameters.Add(new SqlParameter("@NombreUsuarioWeb", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@NombreUsuarioWeb", toInsert.NombreUsuarioWeb));
            }
            if (toInsert.IsContraseniaUsuarioWebNull)
            {
                command.Parameters.Add(new SqlParameter("@ContraseniaUsuarioWeb", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@ContraseniaUsuarioWeb", toInsert.ContraseniaUsuarioWeb));
            }

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }

                reader = command.ExecuteReader();
                reader.Read();
                toReturn = Convert.ToInt32(reader.GetValue(0));
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }

                if ((connection != null) && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
            return toReturn;
        }

        public static void UpdateUsuarioWeb(DTUsuarioWeb toUpdate)
        {
            SqlTransaction transaction = TransactionManager.GetTransaction();
            SqlConnection connection = null;
            bool useTransaction = (transaction != null);
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }
            string updateCommandText = "UPDATE UsuarioWeb SET [NombreUsuarioWeb]=@NombreUsuarioWeb,[ContraseniaUsuarioWeb]=@ContraseniaUsuarioWeb WHERE [IdUsuarioWeb]=@IdUsuarioWeb";

            SqlCommand command;
            if (useTransaction)
            {
                command = new SqlCommand(updateCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(updateCommandText, connection);
            }

            command.Parameters.Add(new SqlParameter("@IdUsuarioWeb", toUpdate.IdUsuarioWeb));

            if (toUpdate.IsNombreUsuarioWebNull)
            {
                command.Parameters.Add(new SqlParameter("@NombreUsuarioWeb", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@NombreUsuarioWeb", toUpdate.NombreUsuarioWeb));
            }
            if (toUpdate.IsContraseniaUsuarioWebNull)
            {
                command.Parameters.Add(new SqlParameter("@ContraseniaUsuarioWeb", DBNull.Value));
            }
            else
            {
                command.Parameters.Add(new SqlParameter("@ContraseniaUsuarioWeb", toUpdate.ContraseniaUsuarioWeb));
            }


            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                command.ExecuteNonQuery();

            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }

        }

        public static void DeleteUsuarioWeb(Int32 idUsuarioWeb)
        {

            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            string deleteCommandText = "DELETE FROM UsuarioWeb WHERE [IdUsuarioWeb]=@IdUsuarioWeb";

            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }

            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(deleteCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(deleteCommandText, connection);
            }

            command.Parameters.Add(new SqlParameter("@IdUsuarioWeb", idUsuarioWeb));



            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                command.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }

        }

        public static DTUsuarioWeb GetUsuarioWeb(Int32 idUsuarioWeb)
        {

            string selectCommandText = String.Format("SELECT {0} FROM [UsuarioWeb] WHERE [IdUsuarioWeb]=@IdUsuarioWeb", GetUsuarioWebFields());
            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }

            SqlDataReader reader;
            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(selectCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(selectCommandText, connection);
            }

            command.Parameters.Add(new SqlParameter("@IdUsuarioWeb", idUsuarioWeb));


            DTUsuarioWeb current = null;

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                reader = command.ExecuteReader();
                if (reader.Read())
                {
                    current = GetUsuarioWebInfo(reader, 0);
                }

                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }
            return current;
        }

        public static List<DTUsuarioWeb> ListUsuarioWeb()
        {

            List<DTUsuarioWeb> toReturn = new List<DTUsuarioWeb>();
            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString" ].ConnectionString);
            }
            string selectCommandText = String.Format("SELECT {0} FROM [UsuarioWeb]", GetUsuarioWebFields());
            SqlDataReader reader;
            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(selectCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(selectCommandText, connection);
            }

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    DTUsuarioWeb current = GetUsuarioWebInfo(reader, 0);
                    toReturn.Add(current);
                }
                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }
            return toReturn;
        }

        public static string GetUsuarioWebFields()
        {
            return " [UsuarioWeb].[IdUsuarioWeb],[UsuarioWeb].[NombreUsuarioWeb],[UsuarioWeb].[ContraseniaUsuarioWeb] ";
        }

        public static DTUsuarioWeb GetUsuarioWebInfo(SqlDataReader reader, int index)
        {
            DTUsuarioWeb current = new DTUsuarioWeb();
            object temp;

            temp = reader.GetValue(index);
            if (temp != DBNull.Value)
            {
                current.IdUsuarioWeb = (Int32)temp;
            }
            index++;
            temp = reader.GetValue(index);
            if (temp != DBNull.Value)
            {
                current.NombreUsuarioWeb = (String)temp;
            }
            index++;
            temp = reader.GetValue(index);
            if (temp != DBNull.Value)
            {
                current.ContraseniaUsuarioWeb = (String)temp;
            }
            index++;


            return current;
        }


        public static DTUsuarioWeb GetUsuarioWebByName(string username)
        {
            string selectCommandText = String.Format("SELECT {0} FROM [UsuarioWeb] WHERE [NombreUsuarioWeb]=@NombreUsuarioWeb", GetUsuarioWebFields());
            SqlTransaction transaction = TransactionManager.GetTransaction();
            bool useTransaction = (transaction != null);
            SqlConnection connection = null;
            if (!useTransaction)
            {
                connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TorrakDBConnectionString"].ConnectionString);
            }

            SqlDataReader reader;
            SqlCommand command;

            if (useTransaction)
            {
                command = new SqlCommand(selectCommandText, transaction.Connection, transaction);
            }
            else
            {
                command = new SqlCommand(selectCommandText, connection);
            }

            command.Parameters.Add(new SqlParameter("@NombreUsuarioWeb", username));


            DTUsuarioWeb current = null;

            try
            {
                if (!useTransaction)
                {
                    connection.Open();
                }
                reader = command.ExecuteReader();
                if (reader.Read())
                {
                    current = GetUsuarioWebInfo(reader, 0);
                }

                reader.Close();
            }
            catch
            {
                throw;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                {
                    connection.Close();
                }
            }
            return current;
        }
    }
}